/* Subversion Info: $Id: $ */

/* Drop the current Aircraft table */
PROMPT Dropping table: AIRCRAFT
set term off
DROP TABLE aircraft CASCADE constraints;
set term on

/* Drop the current Aircraft PK sequence */
PROMPT Dropping sequence: AIRCRAFT_PK_SEQ
set term off
DROP SEQUENCE aircraft_pk_seq;
set term on

/* Aircraft PK sequence to be used for auto NUMBER PRIMARY KEY sequence */
PROMPT Creating sequence: AIRCRAFT_PK_SEQ
set term off
CREATE SEQUENCE aircraft_pk_seq 
INCREMENT BY 1
START WITH 1
NOCACHE;
set term on

/* Create the Aircraft table */
PROMPT Creating table: AIRCRAFT
set term off
CREATE TABLE aircraft
  (
     id         NUMBER(2), 
     name VARCHAR2(5) NOT NULL, 
     alias VARCHAR2(32), 
     xmlschema XMLTYPE,
     constraint aircraft_pk primary key (id)
  );

GRANT SELECT, INSERT, UPDATE, DELETE ON ftdbadm.aircraft TO ftdbuser;
set term on

/* Aircraft insert trigger */
PROMPT Creating trigger: AIRCRAFT_PK_TRIG
set term off
CREATE OR REPLACE TRIGGER aircraft_pk_trig 
  before INSERT ON aircraft
  FOR each ROW
BEGIN
    SELECT aircraft_pk_seq.nextval
    INTO   :new.id
    FROM   dual;
END;
/
set term on

/* Commit all installation changes */
set term off
COMMIT;
set term on

PROMPT
PROMPT Configuration Complete: AIRCRAFT
PROMPT